package com.example.demo.common;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.example.demo.exception.BusinessException;
import lombok.RequiredArgsConstructor;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

@Component
@RequiredArgsConstructor
public class JdbcCon {
    private final DataSource dataSource;

    @Value("${lycustome.databaseName}")
    private String dataBase;

    private String sql = "";
    private List<Object> param = new ArrayList<>();
    private List<Object> column = new ArrayList<>();

    private Connection con = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;

    public void setSql(String val) {
        this.sql = val;
    }

    public void setDatabase(String val) {
        this.dataBase = val;
    }

    public void setField(String val) {
        sql = "select " + val;
    }

    public void setTable(String val) {
        sql += " from " + val;
    }

    public void setWhere(String val) {
        sql += " where " + val;
    }

    public void setOrder(String val) {
        sql += " order by " + val;
    }

    public void setGroup(String val) {
        sql += " group by " + val;
    }

    public void setLimit(String val) {
        sql += " limit " + val;
    }

    public void addParam(Object value) {
        param.add(value);
    }

    public void addCollect(String field, Object value) {
        column.add(field);
        param.add(value);
    }

    private void clearParam() {
        param.clear();
    }

    private void clearCollect() {
        column.clear();
        param.clear();
    }

    private void assembleParam() {
        if (param.size() <= 0) {
            return;
        }

        try {
            int count = param.size();
            for (int i = 0; i < count; i++) {
                Object obj = param.get(i);
                int index = i + 1;

                if (obj instanceof String) {
                    ps.setString(index, obj.toString());
                }
                else if (obj instanceof Integer) {
                    ps.setInt(index, (Integer) obj);
                }
                else if (obj instanceof Double) {
                    ps.setDouble(index, (Double) obj);
                }
                else if (obj instanceof Float) {
                    ps.setFloat(index, (Float) obj);
                }
                else if (obj instanceof Long) {
                    ps.setLong(index, (Long) obj);
                }
                else if (obj instanceof BigDecimal) {
                    ps.setBigDecimal(index, (BigDecimal) obj);
                }
                else {
                    throw new BusinessException("参数类型异常");
                }
            }
        }
        catch (Exception ex) {
            throw new BusinessException("参数类型异常");
        }
    }

    public void open() {
        try {
            con = dataSource.getConnection();
            ps = con.prepareStatement(sql);
            assembleParam();
            rs = ps.executeQuery();
        }
        catch (Exception ex) {
            clearParam();
            close();

            throw new BusinessException("open异常：" + ex);
        }
    }

    public boolean isEmpty() {
        boolean result;

        try {
            result = rs.next();
        }
        catch (Exception ex) {
            clearParam();
            close();

            throw new BusinessException("isEmpty异常：" + ex);
        }

        return result;
    }

    public String getValue(String field) {
        String result = "";

        try {
            result = rs.getString(field);
        }
        catch (Exception ex) {
            clearParam();
            close();

            throw new BusinessException("getValue异常：" + ex);
        }

        return result;
    }

    public void close() {
        if (rs != null) {
            try {
                rs.close();
            }
            catch (Exception ignored) {}
        }
        if (ps != null) {
            try {
                ps.close();
            }
            catch (Exception ignored) {}
        }
        if (con != null) {
            try {
                con.close();
            }
            catch (Exception ignored) {}
        }
    }

    public int execute() {
        int result;

        try {
            con = dataSource.getConnection();
            ps = con.prepareStatement(sql);
            assembleParam();
            result = ps.executeUpdate();
        }
        catch (Exception ex) {
            throw new BusinessException("execute异常：" + ex);
        }
        finally {
            clearCollect();
            close();
        }

        return result;
    }

    public void insertBatch(List<List<Object>> paramList) {
        try {
            con = dataSource.getConnection();
            con.setAutoCommit(false);
            ps = con.prepareStatement(sql);

            //批量设置数据
            for (List<Object> temp : paramList) {
                param = temp;
                assembleParam();
                ps.addBatch();
            }

            ps.executeBatch();
            ps.clearBatch();
            con.commit();
        }
        catch (Exception ex) {
            throw new BusinessException("insertBatch异常：" + ex);
        }
        finally {
            if (con != null) {
                try {
                    con.setAutoCommit(true);
                }
                catch (Exception ignored) {}
            }
            clearParam();
            close();
        }
    }

    private String getFieldName(String table) {
        String result = "";

        try {
            sql = "select a.column_name from information_schema.columns a where a.table_name = '" + table + "' and a.table_schema = '" + dataBase + "'";
            con = dataSource.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (isEmpty()) {
                if (result.equals("")) {
                    result += getValue("column_name");
                }
                else {
                    result += ", " + getValue("column_name");
                }
            }
        }
        catch (Exception ex) {
            throw new BusinessException("getFieldName异常：" + ex);
        }
        finally {
            close();
        }

        return result;

    }

    private String[] getFieldList(String field) {
        field = field.replaceAll("distinct ", "");
        field = field.replaceAll(", ", ",");
        String[] fieldList = field.split(",");
        int fieldCount = fieldList.length;

        for (int i = 0; i < fieldCount; i++) {
            if (fieldList[i].contains(" as ")) {
                fieldList[i] = fieldList[i].split(" as ")[1];
            }
            if (fieldList[i].contains(".")) {
                fieldList[i] = fieldList[i].split(".")[1];
            }
        }

        return fieldList;
    }

    public int insertInfo(String table) {
        int count = column.size();
        int result;

        sql = "insert into " + table + "(";
        for (int i = 0; i < count; i++) {
            if (i == count - 1) {
                sql += column.get(i) + ")";
            }
            else {
                sql += column.get(i) + ", ";
            }
        }
        sql += " values(";
        for (int i = 0; i < count; i++) {
            if (i == count - 1) {
                sql += "?)";
            }
            else {
                sql += "?, ";
            }
        }

        try {
            result = execute();
        }
        catch (Exception ex) {
            throw new BusinessException("insertInfo异常：" + ex);
        }

        return result;
    }

    public int updateInfo(String table) {
        int count = column.size();
        int result;

        sql = "update " + table + " set ";
        for (int i = 0; i < count; i++) {
            if (i == count - 1) {
                sql += " where " + column.get(i) + " = ?";
            }
            else if (i == count - 2) {
                sql += column.get(i) + " = ? ";
            }
            else {
                sql += column.get(i) + " = ?, ";
            }
        }

        try {
            result = execute();
        }
        catch (Exception ex) {
            throw new BusinessException("updateInfo异常：" + ex);
        }

        return result;
    }

    public int deleteInfo(String table) {
        int count = column.size();
        int result;

        sql = "delete from " + table + " where ";
        for (int i = 0; i < count; i++) {
            if (i == 0) {
                sql += column.get(i) + " = ?";
            }
            else {
                sql += " and " + column.get(i) + " = ?";
            }
        }

        try {
            result = execute();
        }
        catch (Exception ex) {
            throw new BusinessException("deleteInfo异常：" + ex);
        }

        return result;
    }

    public JSONObject getInfo(String field, String table, String where) {
        JSONObject result = null;

        try {
            if (field.equals("*")) {
                field = getFieldName(table);
            }
            setField(field);
            setTable(table);
            setWhere(where);

            String[] fieldList = getFieldList(field);

            open();
            if (isEmpty()) {
                result = new JSONObject();
                for (String s : fieldList) {
                    result.put(s, getValue(s));
                }
            }
        }
        catch (Exception ex) {
            throw new BusinessException("getInfo异常：" + ex);
        }
        finally {
            clearParam();
            close();
        }

        return result;
    }

    public JSONArray getList(String field, String table, String where) {
        return getList(field, table, where, null, null, null);
    }

    public JSONArray getList(String field, String table, String where, String order) {
        return getList(field, table, where, order, null, null);
    }

    public JSONArray getList(String field, String table, String where, String order, String group) {
        return getList(field, table, where, order, group, null);
    }

    public JSONArray getList(String field, String table, String where, String order, String group, String limit) {
        JSONArray result;

        try {
            setField(field);
            setTable(table);
            setWhere(where);
            if (group != null) {
                setGroup(group);
            }
            if (order != null) {
                setOrder(order);
            }
            if (limit != null) {
                setLimit(limit);
            }

            String[] fieldList = getFieldList(field);
            result = new JSONArray();

            open();
            while (isEmpty()) {
                JSONObject info = new JSONObject();
                for (String s : fieldList) {
                    info.put(s, getValue(s));
                }

                result.add(info);
            }
        }
        catch (Exception ex) {
            throw new BusinessException("getList异常：" + ex);
        }
        finally {
            clearParam();
            close();
        }

        return result;
    }
}
